package com.scalar.chiptrack.gpintegration.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.gpintegration.LineItemDetail;
import com.scalar.chiptrack.gpintegration.LotDetail;
import com.scalar.chiptrack.gpintegration.OrderDetail;

public class GPIntegrationDAO 
{
    public static OrderDetail getShipmentInfo() throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        OrderDetail orderDetail = null;
        LineItemDetail lineItemDetail = null;
        LotDetail lotDetail 	= null;
        ArrayList<LineItemDetail> lineItemDetailsList = new ArrayList<LineItemDetail>();
        ArrayList<LotDetail> lotDetailList = null;
        double totalAmount = 0.0;
        int lineItemCount  = 0;
        
        try
        {
            conn  	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "SELECT TOP 1 CST.CUSTOMERSHIPPING_ID, CST.inv_no, CST.CST_SHIP_DATE, C_NUMBER, C.C_NAME, L.L_VENDOR_NAME, ISNULL(B.TAG_NAME, 'BILL_0001') AS TAG_NAME, B.ADDRESS_1, B.ADDRESS_2, B.ADDRESS_3, B.ADDRESS_4, B.CITY, B.STATE, B.ZIPCODE, B.COUNTRY, B.ZIPCODE,B.EMAIL, B.PHONE_NUMBER, B.FAX_NUMBER, B.CONTACT_PERSON, SO.SO_SHIPPING_METHOD, ISNULL( S.TAG_NAME, 'SHIP_0001') AS SHIP_ADDRESS_CODE, S.ADDRESS_1 AS SHIP_ADDRESS_ADDRESS_1, S.ADDRESS_2 AS SHIP_ADDRESS_2, S.ADDRESS_3 AS SHIP_ADDRESS_3, S.ADDRESS_4 AS SHIP_ADDRESS_4, S.CITY AS SHIP_ADDRESS_CITY, S.STATE AS SHIP_ADDRESS_STATE, S.ZIPCODE AS SHIP_ADDRESS_ZIPCODE, S.COUNTRY AS SHIP_ADDRESS_COUNTRY, S.ZIPCODE AS SHIP_ADDRESS_ZIPCODE,S.EMAIL AS SHIP_ADDRESS_EMAIL, S.PHONE_NUMBER AS SHIP_ADDRESS_PHONE, S.FAX_NUMBER AS SHIP_ADDRESS_FAX, S.CONTACT_PERSON AS SHIP_ADDRESS_CONTACT, SO.SALESORDER_NO, SO_CUSTOMER_PO_NUMBER, SO_COMMENTS, SHIPPING_TERMS, DELIVERY_TERMS,(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO_ODM_ID) AS END_CUSTOMER,(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO_ENDCUSTOMER_ID) AS END_USER, CST_AW_BILL_NUMBER,  CASE WHEN (SELECT COUNT(*) FROM CUSTOMER_SHIPPING_TABLE WHERE SALESORDER_ID = SO.SalesOrder_ID AND CST_CONFIRMED_ON < CST.CST_CONFIRMED_ON AND CST_STATUS = 'SHIPPED') > 0 THEN 0.0 ELSE  SO_frieght_Tax END AS frieght, SO_USER_FIELD1 FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO, CUSTOMER C, LOCATION L, ADDRESS B, ADDRESS S WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = C.CUSTOMER_ID AND S.ADDRESS_ID = CST.CST_TO_ADDRESS_ID AND B.ADDRESS_ID = CST.CST_BILLING_ADDRESS_ID AND L.ADDRESS_ID = CST.CST_LOCATION_ADDRESS_ID AND L.L_Type= 'Finished Goods' AND CST.CST_INVOICED = 0 AND CST_STATUS = 'SHIPPED' AND SO_IS_SAMPLE = 1" );
            rs 		= pstmt.executeQuery();

	        while( rs.next() )
	        {
                orderDetail = new OrderDetail();
                orderDetail.setCustomerShippingId( rs.getInt("CUSTOMERSHIPPING_ID") );
                orderDetail.setPDocumentNumber( rs.getString( "inv_no" ) );
                orderDetail.setPDocumentType( "Invoice" );
                orderDetail.setPDocumentDate( rs.getString( "CST_SHIP_DATE" ) );
                orderDetail.setPIDCustomer( rs.getString( "C_NUMBER" ) );
                orderDetail.setPLocationCode( rs.getString("L_VENDOR_NAME" ) );
                orderDetail.setPCustomerClassID( "" ); 									// TO BE DONE
                orderDetail.setPBillToAddressID( rs.getString("TAG_NAME") );
                orderDetail.setPBillToName( rs.getString("ADDRESS_1") );
                orderDetail.setPBillToAddress1( rs.getString("ADDRESS_2") );
                orderDetail.setPBillToAddress2( rs.getString("ADDRESS_3") );
                orderDetail.setPBillToAddress3( rs.getString("ADDRESS_4") );
                orderDetail.setPBillToCity( rs.getString("CITY") );
                orderDetail.setPBillToStateCode( rs.getString("STATE") );
                orderDetail.setPBillToZip( rs.getString("ZIPCODE") );
                orderDetail.setPBillToCountryCode( rs.getString("COUNTRY") );
                orderDetail.setPBillToEmail( rs.getString("email") );
                orderDetail.setPBillToPhone( rs.getString("PHONE_NUMBER") );
                orderDetail.setPBillToFax( rs.getString("FAX_NUMBER") );
                orderDetail.setPContactPerson( rs.getString("CONTACT_PERSON") );
                orderDetail.setPTaxAmount( 0.0 ); 										// TO BE DONE
                orderDetail.setPFreightAmount( 0.0 ); 									// TO BE DONE
                orderDetail.setPMiscellaneousAmount( 0.0 ); 							// TO BE DONE
                orderDetail.setPDiscountAmount( 0.0 ); 									// TO BE DONE
                orderDetail.setPTotal( 0.0 ); 											// TO BE DONE
                orderDetail.setPShipMethodCode( rs.getString( "SO_SHIPPING_METHOD" ) );
                orderDetail.setPShipToAddressID( rs.getString("SHIP_ADDRESS_CODE") );
                orderDetail.setPShipToName( rs.getString("SHIP_ADDRESS_ADDRESS_1") );
                orderDetail.setPShipToAddress1( rs.getString("SHIP_ADDRESS_2") );
                orderDetail.setPShipToAddress2( rs.getString("SHIP_ADDRESS_3") );
                orderDetail.setPShipToAddress3( rs.getString("SHIP_ADDRESS_4") );
                orderDetail.setPShipToCity( rs.getString("SHIP_ADDRESS_CITY") );
                orderDetail.setPShipToStateCode( rs.getString("SHIP_ADDRESS_STATE") );
                orderDetail.setPShipToZip( rs.getString("SHIP_ADDRESS_ZIPCODE") );
                orderDetail.setPShipToCountryCode( rs.getString("SHIP_ADDRESS_COUNTRY") );
                orderDetail.setPShipToEmail( rs.getString("SHIP_ADDRESS_EMAIL") );
                orderDetail.setPShipToPhone( rs.getString("SHIP_ADDRESS_PHONE") );
                orderDetail.setPShipToFax( rs.getString("SHIP_ADDRESS_FAX") );
                orderDetail.setPOrderConfirmationNumber( rs.getString("SO_CUSTOMER_PO_NUMBER") );
                orderDetail.setPCommentText( rs.getString( "SO_COMMENTS" ) );
                orderDetail.setPUserDefined2( rs.getString( "CST_AW_BILL_NUMBER" ) );
                orderDetail.setPCustomerPOID( rs.getString( "SO_CUSTOMER_PO_NUMBER" ) );
                orderDetail.setPUserDefined3( rs.getString( "END_USER" ) );
                orderDetail.setPUserDefined4( rs.getString( "SALESORDER_NO" ) );
                orderDetail.setPFreightAmount( rs.getDouble( "frieght" ) );
                orderDetail.setPUserDefined1( rs.getString( "SO_USER_FIELD1" ) );
	        }

	        if( orderDetail != null )
	        {
	        	try { rs.close(); pstmt.close();} catch ( SQLException excp ) {}
	        	
	        	totalAmount = orderDetail.getPFreightAmount();
	            pstmt 	= conn.prepareStatement( "SELECT CSLIT_ID, COOL_LINE_ITEM_NO, P.P_MARKETING_PART_NUMBER, P.P_COMMENTS, CSLIT_QTY_SHIPPED, COOL_UNIT_COST, (SELECT CASE WHEN (SELECT CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CQC_END_DATE >= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101) AND CQC_START_DATE <= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101)) = 1 THEN Q1_STANDARD_COST WHEN (SELECT CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CQC_END_DATE >= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101) AND CQC_START_DATE <= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101)) = 2 THEN Q2_STANDARD_COST WHEN (SELECT CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CQC_END_DATE >= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101) AND CQC_START_DATE <= CONVERT( VARCHAR,CURRENT_TIMESTAMP, 101)) = 3 THEN Q3_STANDARD_COST ELSE Q4_STANDARD_COST END FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = P.PROCESSSTEP_ID  AND YEAR(FIN_YEAR) = YEAR(CURRENT_TIMESTAMP) ) AS UNIT_COST, CONVERT(VARCHAR,ISNULL(COOL_REVISED_DATE, COOL_SCHEDULE_DATE), 101) AS REQUEST_DATE  FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND CUSTOMERSHIPPING_ID = ?" );
	            
	            pstmt.setInt( 1, orderDetail.getCustomerShippingId() );
	            rs 		= pstmt.executeQuery();

		        while( rs.next() )
		        {
		        	lineItemDetail = new LineItemDetail();
		        	lineItemDetail.setCslit_id( rs.getInt( "CSLIT_ID" ) );
		        	lineItemDetail.setPDocumentNumber( orderDetail.getPDocumentNumber() );
		        	lineItemDetail.setPDocumentType( orderDetail.getPDocumentType() );
		        	lineItemDetail.setPLineItemSequence( rs.getString( "COOL_LINE_ITEM_NO" ) );
		        	lineItemDetail.setPItemNumber( rs.getString( "P_MARKETING_PART_NUMBER" ) );
		        	lineItemDetail.setPItemDescription( rs.getString( "COOL_DESCRIPTION" ) );
		        	lineItemDetail.setPQTY( rs.getInt( "CSLIT_QTY_SHIPPED" ) );
		        	lineItemDetail.setPUnitPrice( rs.getDouble( "COOL_UNIT_COST" ) );
		        	lineItemDetail.setPMarkdownAmount( lineItemDetail.getPQTY()*lineItemDetail.getPUnitPrice() );
		        	lineItemDetail.setPUnitCost( rs.getDouble( "UNIT_COST") );
		        	lineItemDetail.setPStandardCost( rs.getDouble( "UNIT_COST") );
		        	lineItemDetail.setPItemClassCode( "" );
		        	lineItemDetail.setPRequestedShipDate( rs.getString( "REQUEST_DATE" ) );
		        	
		        	lineItemDetailsList.add( lineItemDetail );
		        	totalAmount = totalAmount + ( lineItemDetail.getPQTY() * lineItemDetail.getPUnitPrice() );
		        	lineItemCount++;
		        }

				    orderDetail.setLineItemList( lineItemDetailsList );
			        orderDetail.setPTotal( totalAmount );
				    orderDetail.setPUserDefined5( lineItemCount+"" );
	        }
	        
	        int lotSequence = 16384;
	        
	        if( lineItemDetailsList != null && lineItemDetailsList.size() > 0 )
	        {
	        	for( LineItemDetail  lineItem:lineItemDetailsList )
	        	{
		        	try { rs.close(); pstmt.close();} catch ( SQLException excp ) {}
		        	pstmt 	= conn.prepareStatement( "SELECT Lt.Lot_Number, LT.LOT_Date_Code, CSLNT.CSLNT_QTY_PULL FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, Lot_Table LT WHERE CSLNT.Lot_ID = LT.Lot_Id and CSLNT_QTY_PULL > 0 AND CSLIT_ID = ? " );
		        	pstmt.setInt( 1, lineItem.getCslit_id() );
		        	rs 		= pstmt.executeQuery();
		        	lotSequence = 16384;
					lotDetailList = new ArrayList<LotDetail>();
		        	while( rs.next() )
		        	{
		        		lotDetail = new LotDetail();
		        		lotDetail.setPDocumentNumber( orderDetail.getPDocumentNumber() );
		        		lotDetail.setPDocumentType( orderDetail.getPDocumentType() );
		        		lotDetail.setPLineItemSequence( lineItem.getPLineItemSequence() );
		        		lotDetail.setPComponentSequence( "0" );
		        		lotDetail.setPLotSequence( lotSequence );
		        		lotDetail.setPItemNumber( lineItem.getPItemNumber() );
		        		lotDetail.setPLotNumber( rs.getString("Lot_Number") );
		        		lotDetail.setPLotAttribute1( rs.getString( "LOT_Date_Code" ) );
		        		lotDetail.setPQTY( rs.getInt( "CSLNT_QTY_PULL" ) );
						if ( lotDetail.getPLotAttribute1() != null && lotDetail.getPLotAttribute1().length() > 0  )
						{
							lotDetail.setPLotNumber( lotDetail.getPLotNumber()+"-"+ lotDetail.getPLotAttribute1() );
						} 

		        		lotDetailList.add( lotDetail );
		        		lotSequence = lotSequence + 16384;
		        	}
		        	lineItem.setLotDetailList( lotDetailList );
	        	}	        	
	        }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return orderDetail;
   }


    public static void updateInvoiceStatus (DBTransaction dbTransaction, int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("UPDATE CUSTOMER_SHIPPING_TABLE SET CST_INVOICED = 1 WHERE CUSTOMERSHIPPING_ID = ?");
            pstmt.setInt ( 1, customerShippingId );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Customer Shipping Invoice for Id " + customerShippingId + " into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
}
